package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

type student struct {
	id   int
	name string
	age  int
}

var DB *sql.DB

func main() {
	openAndPing()
	selectOne()
	selectMore()
	insertOne()
}

func openAndPing() (err error) {
	var dsn = "root:root@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True"
	DB, err = sql.Open("mysql", dsn)
	if err != nil {
		return
	}
	err = DB.Ping()
	if err != nil {
		return
	}

	return
}

func selectOne() {
	sqlStr := `select id,name, age from stu where id = ?`
	u1 := new(student)
	err := DB.QueryRow(sqlStr, 1).Scan(&u1.id, &u1.name, &u1.age)
	if err != nil {
		fmt.Println(err)
	}
	fmt.Println(u1)
}

func selectMore() {
	var uArr []student
	sqlStr := `select id,name, age from stu`
	row, err := DB.Query(sqlStr)
	if err != nil {
		fmt.Println(err)
	}
	defer row.Close()
	for row.Next() {
		var u student
		err := row.Scan(&u.id, &u.name, &u.age)
		if err != nil {
			fmt.Println(err)
		}
		uArr = append(uArr, u)
	}
	fmt.Println(uArr)
}

func insertOne() {
	sqlStr := `INSERT INTO stu(name, age) VALUES (?, ?)`
	ret, err := DB.Exec(sqlStr, "aaa", 11)
	if err != nil {
		fmt.Println(err)
	}
	id, err := ret.LastInsertId()
	if err != nil {
		fmt.Println(err)
	}
	fmt.Println(id)
}
